package com.hw.dagl.service.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import com.hw.dagl.model.Dagl_T_Cjtj;
import com.hw.dagl.service.CxtjService;
import com.hw.dao.utils.EntityTools;
import com.hw.dao.utils.OraclePageDaoBase;
import com.hw.utils.StringUtils;

@Service
public class CxtjServiceImpl implements CxtjService {

    public static Log logger = LogFactory.getLog(CxtjServiceImpl.class);

    private static final SimpleDateFormat formatDate = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

    @Resource
    private OraclePageDaoBase oraclePageDaoBase;

    /**
     * 采集统计汇总
     */
    @Override
    public List<Dagl_T_Cjtj> findshtjTotal(String startTime, String endTime, String glbm) {
        List<Object> args = new ArrayList<Object>();
        StringBuffer sql = new StringBuffer("");
        if (!glbm.equals("340000000400")) { //总队车管所
            sql = sql.append("select sum(t.ycjtotal) ycjTotal,t.bmmc from VEH_RECORD_COUNT t  where 1=1 ");

            if (StringUtils.isNotEmpty(startTime)) {
                sql.append(" and t.cjsj >= to_date(?,'yyyy-mm-dd hh24:mi:ss') ");
                args.add(startTime);
            }
            if (StringUtils.isNotEmpty(endTime)) {
                sql.append(" and t.cjsj <= to_date(?,'yyyy-mm-dd hh24:mi:ss') ");
                args.add(endTime);
            }
            sql.append("group by t.bmmc");
        } else {
            sql = sql.append("select sum(t.ycjtotal) ycjTotal,r.bmmc from VEH_RECORD_COUNT t,FRM_DEPARTMENT_LOCAL r where t.ssds=r.glbm ");
            if (StringUtils.isNotEmpty(startTime)) {
                sql.append(" and t.cjsj >= to_date(?,'yyyy-mm-dd hh24:mi:ss') ");
                args.add(startTime);
            }
            if (StringUtils.isNotEmpty(endTime)) {
                sql.append(" and t.cjsj <= to_date(?,'yyyy-mm-dd hh24:mi:ss') ");
                args.add(endTime);
            }
            sql.append("group by r.bmmc");
        }

        logger.info(sql);
        return oraclePageDaoBase.query(sql.toString(), args.toArray(), new RowMapper<Dagl_T_Cjtj>() {
            private EntityTools<Dagl_T_Cjtj> et = new EntityTools<Dagl_T_Cjtj>();

            @Override
            public Dagl_T_Cjtj mapRow(ResultSet arg0, int arg1)
                    throws SQLException {
                Dagl_T_Cjtj cjtj = new Dagl_T_Cjtj();
                et.setColumnsValueByResultSet(arg0, cjtj);
                return cjtj;
            }
        });
    }


    /**
     * 驾驶证采集统计汇总
     */
    @Override
    public List<Dagl_T_Cjtj> findjszcjtjTotal(String startTime, String endTime) {
        List<Object> args = new ArrayList<Object>();
        StringBuffer sql = new StringBuffer(" select a.cjr,a.total,a.dcjTotal,a.dshTotal,a.cjyshTotal,a.cxcjTotal, nvl(round(cjyshTotal/total * 100), 0) percent,nvl(round(cjyshTotal/totalall * 100), 0) percentall from( " +
                "select t.cjr,count(*) total,(select count(*) from vehicle) totalall,\n" +
                "sum(case when t.dazt='0' then 1 else 0 end) dcjTotal,\n" +
                "sum(case when t.dazt='1' then 1 else 0 end) dshTotal,\n" +
                "sum(case when t.dazt='2' then 1 else 0 end) cjyshTotal,\n" +
                "sum(case when t.dazt='3' then 1 else 0 end) cxcjTotal\n" +
                " from driver_record t where 1=1\n");
        SimpleDateFormat formatDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        if (StringUtils.isNotEmpty(startTime)) {
            sql.append(" and t.cjsj >= ? ");
            try {
                args.add(formatDate.parse(startTime));
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }
        if (StringUtils.isNotEmpty(endTime)) {
            sql.append(" and t.cjsj <= ? ");
            try {
                args.add(formatDate.parse(endTime));
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }
        sql.append(" group by t.cjr) a ");
        return oraclePageDaoBase.query(sql.toString(), args.toArray(), new RowMapper<Dagl_T_Cjtj>() {
            private EntityTools<Dagl_T_Cjtj> et = new EntityTools<Dagl_T_Cjtj>();

            @Override
            public Dagl_T_Cjtj mapRow(ResultSet arg0, int arg1)
                    throws SQLException {
                Dagl_T_Cjtj cjtj = new Dagl_T_Cjtj();
                et.setColumnsValueByResultSet(arg0, cjtj);
                return cjtj;
            }
        });
    }

    /**
     * 机动车采集统计汇总
     */
    @Override
    public List<Dagl_T_Cjtj> findjdccjtjTotal(String startTime, String endTime) {
        List<Object> args = new ArrayList<Object>();
        StringBuffer sql = new StringBuffer(" select a.bmmc,a.total,a.dcjTotal,a.dshTotal,a.cjyshTotal,a.cxcjTotal, nvl(round(cjyshTotal/total * 100), 0) percent,nvl(round(cjyshTotal/totalall * 100), 0) percentall from( " +
                "select r.bmmc,count(*) total,(select count(*) from vehicle) totalall,\n" +
                "sum(case when t.dazt='0' then 1 else 0 end) dcjTotal,\n" +
                "sum(case when t.dazt='1' then 1 else 0 end) dshTotal,\n" +
                "sum(case when t.dazt='2' then 1 else 0 end) cjyshTotal,\n" +
                "sum(case when t.dazt='3' then 1 else 0 end) cxcjTotal\n" +
                " from vehicle_record t,FRM_DEPARTMENT_LOCAL r where t.glbm=r.glbm\n");
        SimpleDateFormat formatDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        if (StringUtils.isNotEmpty(startTime)) {
            sql.append(" and t.cjsj >= ? ");
            try {
                args.add(formatDate.parse(startTime));
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }
        if (StringUtils.isNotEmpty(endTime)) {
            sql.append(" and t.cjsj <= ? ");
            try {
                args.add(formatDate.parse(endTime));
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }
        sql.append(" group by r.bmmc) a ");
        return oraclePageDaoBase.query(sql.toString(), args.toArray(), new RowMapper<Dagl_T_Cjtj>() {
            private EntityTools<Dagl_T_Cjtj> et = new EntityTools<Dagl_T_Cjtj>();

            @Override
            public Dagl_T_Cjtj mapRow(ResultSet arg0, int arg1)
                    throws SQLException {
                Dagl_T_Cjtj cjtj = new Dagl_T_Cjtj();
                et.setColumnsValueByResultSet(arg0, cjtj);
                return cjtj;
            }
        });
    }

    /**
     * 工作量统计
     */
    @Override
    public List<Dagl_T_Cjtj> findgzl(String startTime, String endTime) throws ParseException {
        List<Object> args = new ArrayList<>();
        StringBuffer sql = new StringBuffer();
        sql.append("select a.ycjTotal,b.XM cjr from ");
        sql.append("(select count(*) ycjTotal,t.cjr from VEHICLE_RECORD t where 1=1 ");
        if (StringUtils.isNotEmpty(startTime)) {
            sql.append(" and t.cjsj >= to_date(?,'yyyy-mm-dd hh24:mi:ss') ");
            args.add(startTime);
        }
        if (StringUtils.isNotEmpty(endTime)) {
            sql.append(" and t.cjsj <= to_date(?,'yyyy-mm-dd hh24:mi:ss') ");
            args.add(endTime);
        }
        sql.append(" group by t.cjr) a,SYS_USER b where a.cjr=b.YHDH ");
        sql.append("union all select count(*) ycjTotal, t.cjr from VEHICLE_RECORD t where 1 = 1 and t.sjly<>'0' ");
        if (StringUtils.isNotEmpty(startTime)) {
            sql.append(" and t.cjsj >= to_date(?,'yyyy-mm-dd hh24:mi:ss') ");
            args.add(startTime);
        }
        if (StringUtils.isNotEmpty(endTime)) {
            sql.append(" and t.cjsj <= to_date(?,'yyyy-mm-dd hh24:mi:ss') ");
            args.add(endTime);
        }
        sql.append(" group by t.cjr ");
        logger.info("人员工作量统计:"+sql);
        return oraclePageDaoBase.query(sql.toString(), args.toArray(), new RowMapper<Dagl_T_Cjtj>() {
            private EntityTools<Dagl_T_Cjtj> et = new EntityTools<Dagl_T_Cjtj>();

            @Override
            public Dagl_T_Cjtj mapRow(ResultSet arg0, int arg1)
                    throws SQLException {
                Dagl_T_Cjtj cjtj = new Dagl_T_Cjtj();
                et.setColumnsValueByResultSet(arg0, cjtj);
                return cjtj;
            }
        });
    }


	@Override
	public List<Dagl_T_Cjtj> findNotUploadcount(String startTime, String endTime) {
		StringBuffer sql =new StringBuffer("select ssds,dsmc,sum(today_notuploadnum) t_total,sum(yesteday_notuploadnum) y_total,\n" +
				"decode(sum(yesteday_notuploadnum),0,100,round((sum(today_notuploadnum)-sum(yesteday_notuploadnum))/sum(yesteday_notuploadnum)*100,2)) hb from (\n" + 
				"select r.ssds,r.dsmc,nvl(t.notuploadnum,0) today_notuploadnum,'0' yesteday_notuploadnum,t.count_data from EVERYCITY_COUNT t,frm_dep r where r.ssds=t.ssds\n" + 
				"and t.count_data=trunc(to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss'))");
		if(StringUtils.isNotEmpty(startTime)&&StringUtils.isValidDate(startTime)){
			sql.append("and t.count_data=trunc(to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss'))");
		}
		sql.append("union all select r.ssds,r.dsmc,'0' today_notuploadnum,nvl(t.notuploadnum,0) yesteday_notuploadnum,t.count_data from EVERYCITY_COUNT t,frm_dep r where r.ssds=t.ssds\n");
		if(StringUtils.isNotEmpty(startTime)&&StringUtils.isValidDate(startTime)){
			sql.append("and t.count_data=trunc(to_date('"+startTime+"','yyyy-mm-dd hh24:mi:ss'))-1");
		}
		sql.append(") group by ssds,dsmc  order by ssds");
		logger.info("接口未上传数据统计:"+sql);
		return oraclePageDaoBase.query(sql.toString(), new Object[]{}, new RowMapper<Dagl_T_Cjtj>() {
            private EntityTools<Dagl_T_Cjtj> et = new EntityTools<Dagl_T_Cjtj>();

            @Override
            public Dagl_T_Cjtj mapRow(ResultSet arg0, int arg1)
                    throws SQLException {
                Dagl_T_Cjtj cjtj = new Dagl_T_Cjtj();
                et.setColumnsValueByResultSet(arg0, cjtj);
                return cjtj;
            }
        });
	}

}
